package com.cuit.qx.utils;


import com.cuit.qx.bean.WeatherStation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelReadUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelReadUtil.class);

    public static void main(String[] args) {
        File file=new File("C:\\Users\\Administrator\\Desktop\\自动站.xlsx");
        List<WeatherStation> weatherStations = readExcel(file, 1, null);
        for (WeatherStation weatherStation : weatherStations) {
            System.out.println(weatherStation.toString());

        }

    }


    public static List<WeatherStation> readExcel(File file, int ignoreRow, String siteNum) {
        if (file.getName().toLowerCase().endsWith(".xlsx")) {
            return readExcelForXlsx(file, ignoreRow,siteNum);
//        } else if (file.getName().toLowerCase().endsWith(".xls")) {
//            return readExcelForXls(file, ignoreRow);
        }
        return null;
    }
    /**
     * 读取Excel xlsx后缀名文件数据
     *
     * @param file
     */
    private static List<WeatherStation> readExcelForXlsx(File file, int ignoreRow,String siteNum) {
        if (!file.exists()) {
            logger.error("{}文件不存在", file.getName());
            return null;
        }
        int rowSize = 0;
        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<WeatherStation> listAll =new ArrayList<>();
        try (BufferedInputStream in = new BufferedInputStream(new FileInputStream(file))) {
            XSSFWorkbook workbook = null;
            try {
                workbook = new XSSFWorkbook(in);
            } catch (IOException e) {
                e.printStackTrace();
            }
            XSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                if(sheetIndex>0){
                    break;
                }
                XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                ArrayList<ArrayList<String>> lists = new ArrayList<>();
                for (int rowIndex = ignoreRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    XSSFRow row = sheet.getRow(rowIndex);
                    if (null == row) {
                        continue;
                    }

                    int tempRowSize = row.getLastCellNum() + 1;
                    if (tempRowSize > rowSize) {
                        rowSize = tempRowSize;
                    }

                    ArrayList<String> list = new ArrayList<>();
                    int col = 0;

                    for (int colIndex = 0; colIndex <= row.getLastCellNum(); colIndex++) {
                        cell = row.getCell(colIndex);
                        String value ;
                        double var;
                        if (cell != null) {
                            int cellType = cell.getCellType();

                            switch (cellType) {
                                case 0:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        value = String.valueOf(cell.getDateCellValue());
                                    } else {
                                    //    value=String.valueOf(new DecimalFormat("0.000").format(cell.getNumericCellValue()));
                                        value=String.valueOf(Double.parseDouble(String.valueOf(cell.getNumericCellValue())));
                                    }
                                    break;
                                case 1:
                                    value = cell.getStringCellValue().replace(" ","");
                                    break;
                                case 2:
                                    value = String.valueOf(cell.getCellFormula());
                                    break;
                                case 4:
                                    value = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case 5:
                                    value = String.valueOf(cell.getErrorCellValue());
                                    break;
                                default:
                                    value = "";
                            }
                            if (!StringUtils.isEmpty(value)) {
                                list.add(value);
                            } else {
                                col++;
                            }
                        }
                    }
                    if (col == row.getRowNum()) {
                        continue;
                    }

                    if (list.size() > 0) {
                        lists.add(list);
                    }

                }

                for (ArrayList<String> list1 : lists) {
                    WeatherStation weatherStation = new WeatherStation();
                    weatherStation.setSitesId(UUID.randomUUID().toString().replaceAll("-", ""));
                    weatherStation.setStationName(list1.get(0));
                    weatherStation.setStationAddress(list1.get(2));
                    weatherStation.setStationCounty(list1.get(3));
                    weatherStation.setStationType(list1.get(4).substring(0, list1.get(4).length() - 2));
                    weatherStation.setState(list1.get(1));
                    weatherStation.setStationNumber(list1.get(5));
                    weatherStation.setLongitude(Double.parseDouble(list1.get(6)));
                    weatherStation.setLatitude(Double.parseDouble(list1.get(7)));
                    weatherStation.setCreateTime(simpleDateFormat.format(new Date()));
                    listAll.add(weatherStation);
                }

//
//                // 第三页
//                if(sheetIndex==2){
//                    for (ArrayList<String> list2 : lists) {
//                        Site site=new Site();
//                        site.setSiteId(list2.get(0));
//                        site.setSiteName(list2.get(1));
//                        site.setLongitude(list2.get(2));
//                        site.setLatitude(list2.get(3));
//                        site.setType(1);
//                        listAll.add(site);
//                    }
//                }
//
//                // 第四页
//                if(sheetIndex==3){
//                    for (ArrayList<String> list2 : lists) {
//                        Site site=new Site();
//                        site.setSiteId(list2.get(0));
//                        site.setSiteName(list2.get(1));
//                        site.setLongitude(list2.get(2));
//                        site.setLatitude(list2.get(3));
//                        System.out.println(list2.get(2)+"   "+list2.get(3));
//                        site.setType(2);
//                        listAll.add(site);
//                    }
//                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listAll;
    }

    public static Double returnData(String str){
        if (str.length()>15 || str.equals(".")){
            return 0.0;
        }
        double v = Double.parseDouble(str);
        return v;
    }

    public static String date(String str){
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date=new Date(str);
        Calendar calendar=Calendar.getInstance();
        calendar.setTime(date);
        return sdf.format(calendar.getTime());
    }


    /**
     * 读取excel xls后缀名文件
     *
     * @param file
     * @param ignoreRow
     * @return
     */
    private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXls(File file, int ignoreRow) {
        HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>();
        if (!file.exists()) {
            logger.error("{}文件不存在", file.getName());
            return null;
        }
        int rowSize = 0;
        try {
            BufferedInputStream bufferedInputStream = new BufferedInputStream(new FileInputStream(file));
            HSSFWorkbook workbook = new HSSFWorkbook(bufferedInputStream);
            HSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                ArrayList<ArrayList<String>> lists = new ArrayList<>();
                for (int rowIndex = ignoreRow; rowIndex < sheet.getLastRowNum(); rowIndex++) {
                    HSSFRow row = sheet.getRow(rowIndex);
                    if (null == row) {
                        continue;
                    }

                    int tempRowSize = row.getLastCellNum() + 1;
                    if (tempRowSize > rowSize) {
                        rowSize = tempRowSize;
                    }
                    ArrayList<String> list = new ArrayList<>();
                    int col = 0;
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        cell = row.getCell(colIndex);
                        String value = "";
                        if (cell != null) {
                            int cellType = cell.getCellType();

                            switch (cellType) {
                                case 0:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        value = String.valueOf(cell.getDateCellValue());
                                    } else {
                                        value = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue()));
                                    }
                                    break;
                                case 1:
                                    value = String.valueOf(cell.getStringCellValue());
                                    break;
                                case 2:
                                    value = String.valueOf(cell.getCellFormula());
                                    break;
                                case 3:
                                    value = "";
                                    break;
                                case 4:
                                    value = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case 5:
                                    value = String.valueOf(cell.getErrorCellValue());
                                    break;
                                default:
                                    value = "";
                            }
                            if (!StringUtils.isEmpty(value)) {
                                list.add(value);
                            } else {
                                col++;
                            }
                        }
                    }
                    if (col == row.getRowNum()) {
                        continue;
                    }
                    if (list.size() > 0) {
                        lists.add(list);
                    }
                }
                map.put("sheet" + sheetIndex, lists);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }
}
